package com.ruoyi.quartz.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.ruoyi.common.annotation.DataSource;
import com.ruoyi.common.enums.DataSourceType;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.quartz.mapper.YongyouXshtMapper;
import com.ruoyi.quartz.domain.YongyouXsht;
import com.ruoyi.quartz.service.IYongyouXshtService;

/**
 * 销售合同Service业务层处理
 * 
 * @author SYF
 * @date 2025-04-17
 */
@Service
public class YongyouXshtServiceImpl implements IYongyouXshtService 
{
    private static final Logger log = LoggerFactory.getLogger(YongyouXshtServiceImpl.class);

    private static final double PI = 3.14159265358979324;
    private static final double A = 6378245.0;
    private static final double EE = 0.00669342162296594323;
    private static final double X_PI = PI * 3000.0 / 180.0;

    @Autowired
    private YongyouXshtMapper yongyouXshtMapper;

    /**
     * 查询销售合同
     * 
     * @param orderdetailsFirstupcode 销售合同主键
     * @return 销售合同
     */
    @Override
    @DataSource(DataSourceType.SLAVE)
    public YongyouXsht selectYongyouXshtByOrderdetailsFirstupcode(String orderdetailsFirstupcode)
    {
        return yongyouXshtMapper.selectYongyouXshtByOrderdetailsFirstupcode(orderdetailsFirstupcode);
    }

    /**
     * 查询销售合同列表
     * 
     * @param yongyouXsht 销售合同
     * @return 销售合同
     */
    @Override
    @DataSource(DataSourceType.SLAVE)
    public List<YongyouXsht> selectYongyouXshtList(YongyouXsht yongyouXsht)
    {
        return yongyouXshtMapper.selectYongyouXshtList(yongyouXsht);
    }

    /**
     * 新增销售合同
     * 
     * @param yongyouXsht 销售合同
     * @return 结果
     */
    @Override
    @DataSource(DataSourceType.SLAVE)
    public int insertYongyouXsht(YongyouXsht yongyouXsht)
    {
        return yongyouXshtMapper.insertYongyouXsht(yongyouXsht);
    }

    /**
     * 修改销售合同
     * 
     * @param yongyouXsht 销售合同
     * @return 结果
     */
    @Override
    @DataSource(DataSourceType.SLAVE)
    public int updateYongyouXsht(YongyouXsht yongyouXsht)
    {
        return yongyouXshtMapper.updateYongyouXsht(yongyouXsht);
    }

    /**
     * 批量删除销售合同
     * 
     * @param orderdetailsFirstupcodes 需要删除的销售合同主键
     * @return 结果
     */
    @Override
    @DataSource(DataSourceType.SLAVE)
    public int deleteYongyouXshtByOrderdetailsFirstupcodes(String[] orderdetailsFirstupcodes)
    {
        return yongyouXshtMapper.deleteYongyouXshtByOrderdetailsFirstupcodes(orderdetailsFirstupcodes);
    }

    /**
     * 删除销售合同信息
     * 
     * @param orderdetailsFirstupcode 销售合同主键
     * @return 结果
     */
    @Override
    @DataSource(DataSourceType.SLAVE)
    public int deleteYongyouXshtByOrderdetailsFirstupcode(String orderdetailsFirstupcode) {
        return yongyouXshtMapper.deleteYongyouXshtByOrderdetailsFirstupcode(orderdetailsFirstupcode);
    }

    @Override
    @DataSource(DataSourceType.SLAVE)
    public int deleteYongyouXshtByDate(String startTime,String endTime) {
        return yongyouXshtMapper.deleteYongyouXshtByDate(startTime,endTime);
    }

    @Override
    @DataSource(DataSourceType.SLAVE)
    public List<YongyouXsht> selectYongyouXshtByDate(String createTime) {
        return yongyouXshtMapper.selectYongyouXshtByDate(createTime);
    }

    @Override
    @DataSource(DataSourceType.SLAVE)
    public int clearXsht(){
        return yongyouXshtMapper.clearXsht();
    }

    @Override
    @DataSource(DataSourceType.SLAVE)
    public List<Map<String,Object>> getYjgXsht(String year){
        return yongyouXshtMapper.getYjgXsht(year);
    }

    @Override
    @DataSource(DataSourceType.SLAVE)
    public List<Map<String,Object>> getOtherXsht(String year){
        return yongyouXshtMapper.getOtherXsht(year);
    }

    @Override
    @DataSource(DataSourceType.SLAVE)
    public List<Map<String,Object>> getDetailXsht(String year){
        return yongyouXshtMapper.getDetailXsht(year);
    }

    @Override
    @DataSource(DataSourceType.SLAVE)
    public List<Map<String,Object>> selectJianLan(String year,String day){
        return yongyouXshtMapper.selectJianLan(year,day);
    }

    @Override
    @DataSource(DataSourceType.SLAVEDWPROD)
    public void statAraeCustomers(){
        String sql_clear = "delete from bigscreen.syf_district_customers_stat";
        yongyouXshtMapper.impAraeCustomers(sql_clear);
        String sql = "select distinct area from bigscreen.syf_district_customer_info";
        List<Map<String,Object>> areas = yongyouXshtMapper.selectAraeCustomers(sql);
        if(areas.size()>0){
            for(Map<String,Object> area : areas){
                if(area.get("area")!=null){
                    if(area.get("area").toString().equalsIgnoreCase("省外")){
                        String sql_in = "select ccode,area,county,district,count(*) as customers from bigscreen.syf_district_customer_info where area='"+area.get("area")+"' and is_jurisdict=0 GROUP BY 1,2,3,4";
                        List<Map<String,Object>> ins =  yongyouXshtMapper.selectAraeCustomers(sql_in);
                        if(ins.size()>0){
                            for(Map<String,Object> in : ins){
                                String district = in.get("district").toString();
                                String county = in.get("county").toString();
                                int amount = Integer.parseInt(in.get("customers").toString());
                                String name = in.get("area").toString();
//                                String region = district+"-"+county;
                                String region = county;
                                String insert_sql = "insert into bigscreen.syf_district_customers_stat values('"+region+"',"+amount+",'"+name+"')";
                                yongyouXshtMapper.impAraeCustomers(insert_sql);
                            }
                        }
                    }else{
                        String sql_in = "select ccode,area,county,district,count(*) as customers from bigscreen.syf_district_customer_info where area='"+area.get("area")+"' and is_jurisdict=0 GROUP BY 1,2,3,4";
                        List<Map<String,Object>> ins =  yongyouXshtMapper.selectAraeCustomers(sql_in);

                        String sql_out = "select ccode,area,county,district,count(*) as customers from bigscreen.syf_district_customer_info where area='"+area.get("area")+"' and is_jurisdict=1 GROUP BY 1,2,3,4";
                        List<Map<String,Object>> outs =  yongyouXshtMapper.selectAraeCustomers(sql_out);

                        if(ins.size()>0){
                            for(Map<String,Object> in : ins){
                                String district = in.get("district").toString();
                                String county = in.get("county").toString();
                                int amount = Integer.parseInt(in.get("customers").toString());
                                String name = in.get("area").toString();
                                String region = district+"-"+county.substring(0,2);
                                String insert_sql = "insert into bigscreen.syf_district_customers_stat values('"+region+"',"+amount+",'"+name+"')";
                                yongyouXshtMapper.impAraeCustomers(insert_sql);
                            }
                        }

                        if(outs.size()>0){
                            for(Map<String,Object> out : outs){
                                String district = out.get("district").toString();
                                String county = out.get("county").toString();
                                int amount = Integer.parseInt(out.get("customers").toString());
                                String name = out.get("area").toString();
                                String region = district+"-"+county.substring(0,2);
                                String insert_sql = "insert into bigscreen.syf_district_customers_stat values('"+region+"',"+amount+",'"+name+"')";
                                yongyouXshtMapper.impAraeCustomers(insert_sql);
                            }
                        }
                    }
                }
            }
        }
    }

    @Override
    @DataSource(DataSourceType.SLAVEDWPROD)
    public void impAraeCustomers(){
        String sql_del = "delete from bigscreen.syf_district_customer_info;";
        yongyouXshtMapper.impAraeCustomers(sql_del);
        String path = "D:\\syftmp\\template.xlsx";
        try (FileInputStream fis = new FileInputStream(new File(path));
             Workbook workbook = new XSSFWorkbook(fis)) {
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 遍历行
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    String cus_name = "";
                    String color = "";
                    String desc = "";
                    String area = "";
                    String address = "";
                    String ccode = "";
                    String county = "";
                    String district = "";
                    String saleman = "";
                    String isJurisdict = "";
                    String visittime = "";
                    Cell cell0 = row.getCell(0);
                    if (cell0 != null) {
                        cus_name = getCellValue(cell0,null);
                    }
                    Cell cell1 = row.getCell(1);
                    if (cell1 != null) {
                        color = getCellValue(cell1,null);
                    }
                    Cell cell2 = row.getCell(2);
                    if (cell2 != null) {
                        desc = getCellValue(cell2,null);
                    }
                    Cell cell3 = row.getCell(3);
                    if (cell3 != null) {
                        area = getCellValue(cell3,null);
                    }
                    Cell cell4 = row.getCell(4);
                    if (cell4 != null) {
                        address = getCellValue(cell4,null);
                    }
                    Cell cell5 = row.getCell(5);
                    if (cell5 != null) {
                        ccode = getCellValue(cell5,null);
                    }
                    Cell cell6 = row.getCell(6);
                    if (cell6 != null) {
                        county = getCellValue(cell6,null);
                    }
                    Cell cell7 = row.getCell(7);
                    if (cell7 != null) {
                        district = getCellValue(cell7,null);
                    }
                    Cell cell8 = row.getCell(8);
                    if (cell8 != null) {
                        saleman = getCellValue(cell8,null);
                    }
                    Cell cell9 = row.getCell(9);
                    if (cell9 != null) {
                        isJurisdict = getCellValue(cell9,null);
                    }
                    SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Cell cell10 = row.getCell(10);
                    if (cell10 != null) {
                        visittime = getCellValue(cell10,sdft);
                    }
                    String sql = "insert into bigscreen.syf_district_customer_info values('"+cus_name+"','"+color+"','"+desc+"','"+area+"','"+address.replaceAll("来自微信的地点","")+"','"+ccode.replaceAll("\\.0","")+"','"+county+"','"+district+"','"+saleman+"','"+isJurisdict.replaceAll("\\.0","")+"','"+visittime+"',0,0)";
                    yongyouXshtMapper.impAraeCustomers(sql);
                }
            }
            operateLocation();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public void operateLocation(){
        String sql = "select distinct address as address from bigscreen.syf_district_customer_info";
        List<Map<String,Object>> list = yongyouXshtMapper.selectAraeCustomers(sql);
        if(list != null && list.size() > 0){
            for(Map<String,Object> cus : list){
                if(cus.get("address")!=null){
                    String url = "https://restapi.amap.com/v3/geocode/geo?address="+cus.get("address")+"&key=18d558136d0eb207aed7727a087dc79c";
                    try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
                        HttpGet request = new HttpGet(url);
                        try (CloseableHttpResponse response = httpClient.execute(request)) {
                            String responseBody = EntityUtils.toString(response.getEntity());
                            System.out.println("Status Code: " + response.getStatusLine().getStatusCode());
                            System.out.println("Response Body: " + responseBody);
                            String location = "";
                            JSONObject jsonObject = JSON.parseObject(responseBody);
                            JSONArray geocodes = jsonObject.getJSONArray("geocodes");
                            double x = Double.parseDouble(geocodes.getJSONObject(0).getString("location").split(",")[0]);
                            double y = Double.parseDouble(geocodes.getJSONObject(0).getString("location").split(",")[1]);
                            double[] coordinates = gcj02ToWgs84(x,y);
                            String usql = "update bigscreen.syf_district_customer_info set xzb="+coordinates[0]+",yzb="+coordinates[1]+" where address='"+cus.get("address")+"'";
                            yongyouXshtMapper.impAraeCustomers(usql);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    try {
                        Thread.sleep(2000);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    public static double[] gcj02ToWgs84(double lng, double lat) {
        if (outOfChina(lng, lat)) {
            return new double[]{lng, lat};
        } else {
            double dLat = transformLat(lng - 105.0, lat - 35.0);
            double dLng = transformLng(lng - 105.0, lat - 35.0);
            double radLat = lat / 180.0 * PI;
            double magic = Math.sin(radLat);
            magic = 1 - EE * magic * magic;
            double sqrtMagic = Math.sqrt(magic);
            dLat = (dLat * 180.0) / ((A * (1 - EE)) / (magic * sqrtMagic) * PI);
            dLng = (dLng * 180.0) / (A / sqrtMagic * Math.cos(radLat) * PI);
            double mgLat = lat + dLat;
            double mgLng = lng + dLng;

            double wgsLng = lng * 2 - mgLng;
            double wgsLat = lat * 2 - mgLat;

            return new double[]{wgsLng, wgsLat};
        }
    }

    private static boolean outOfChina(double lng, double lat) {
        return (lng < 72.004 || lng > 137.8347) || (lat < 0.8293 || lat > 55.8271);
    }

    private static double transformLat(double x, double y) {
        double ret = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * Math.sqrt(Math.abs(x));
        ret += (20.0 * Math.sin(6.0 * x * PI) + 20.0 * Math.sin(2.0 * x * PI)) * 2.0 / 3.0;
        ret += (20.0 * Math.sin(y * PI) + 40.0 * Math.sin(y / 3.0 * PI)) * 2.0 / 3.0;
        ret += (160.0 * Math.sin(y / 12.0 * PI) + 320 * Math.sin(y * PI / 30.0)) * 2.0 / 3.0;
        return ret;
    }

    private static double transformLng(double x, double y) {
        double ret = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * Math.sqrt(Math.abs(x));
        ret += (20.0 * Math.sin(6.0 * x * PI) + 20.0 * Math.sin(2.0 * x * PI)) * 2.0 / 3.0;
        ret += (20.0 * Math.sin(x * PI) + 40.0 * Math.sin(x / 3.0 * PI)) * 2.0 / 3.0;
        ret += (150.0 * Math.sin(x / 12.0 * PI) + 300.0 * Math.sin(x / 30.0 * PI)) * 2.0 / 3.0;
        return ret;
    }

    public String getCellValue(Cell cell, SimpleDateFormat sdf) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if(date!=null){
                        return sdf.format(date);
                    }else{
                        return ""+cell.getDateCellValue();
                    }
                } else {
                    return ""+cell.getNumericCellValue();
                }
            case BOOLEAN:
                return ""+cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
}
